/**
 * www.easyplatform.cn ©2016
 */
package cn.easyplatform.studio.dao.impl;

import cn.easyplatform.entities.EntityInfo;
import cn.easyplatform.entities.beans.project.DeviceMapBean;
import cn.easyplatform.entities.beans.project.ProjectBean;
import cn.easyplatform.entities.transform.TransformerFactory;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.lang.stream.StringOutputStream;
import cn.easyplatform.studio.dao.DaoException;
import cn.easyplatform.studio.dao.DaoUtils;
import cn.easyplatform.studio.dao.Dialect;
import cn.easyplatform.studio.dao.IdentityDao;
import cn.easyplatform.studio.dao.transaction.JdbcTransactions;
import cn.easyplatform.studio.utils.StringUtil;
import cn.easyplatform.studio.vos.FieldVo;
import cn.easyplatform.studio.vos.LoginUserVo;
import cn.easyplatform.studio.vos.LoginlogVo;
import cn.easyplatform.studio.vos.ProjectRoleVo;
import cn.easyplatform.type.EntityType;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author <a href="mailto:shiny_vc@163.com">陈云亮</a> <br/>
 * @since 2.0.0 <br/>
 */
public abstract class AbstractIdentityDao implements IdentityDao {

    private DataSource dataSource;

    public AbstractIdentityDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    protected abstract Dialect getDialect();

    @Override
    public LoginUserVo getUser(String userId) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select name,password,validDate,validDays,loginFailedTimes,salt,theme," +
                            "editorTheme,defaultProject from ep_user_info where userId=? and state=1");
            stmt.setString(1, userId);
            rs = stmt.executeQuery();
            if (rs.next()) {
                LoginUserVo e = new LoginUserVo();
                e.setUserId(userId);
                e.setName(rs.getString(1));
                e.setPassword(rs.getString(2));
                //e.setType(rs.getInt(3));
                e.setValidDate(rs.getDate(3));
                e.setValidDays(rs.getInt(4));
                e.setLoginFailedTimes(rs.getInt(5));
                e.setSalt(rs.getString(6));
                e.setTheme(rs.getString(7));
                e.setEditorTheme(rs.getString(8));
                e.setDefaultProject(rs.getString(9));
                //String roleId = rs.getString(10);
                //String productID = rs.getString(11);
                /*if (!Strings.isBlank(e.getDefaultProject())) {
                    DaoUtils.closeQuietly(stmt, rs);
                    stmt = conn
                            .prepareStatement("select permissions from ep_role_info where roleId=? and projectId=?");
                    stmt.setString(1, roleId);
                    stmt.setString(2, productID);
                    rs = stmt.executeQuery();
                    if (rs.next()) {
                        String access = rs.getString(1);
                        e.setAuthorization(StudioUtil.fromJson(access));
                    }
                }*/
                return e;
            }
            return null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    public void updateState(String userId, String address, int state) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            if (state == LoginUserVo.STATE_LOCK) {
                pstmt = conn
                        .prepareStatement("update ep_user_info set state=? where userId=?");
                pstmt.setInt(1, state);
                pstmt.setString(2, userId);
            } else if (address != null) {
                pstmt = conn
                        .prepareStatement("update ep_user_info set lastLoginTime=?,ipAddress=?,loginFailedTimes=0 where userId=?");
                pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
                pstmt.setString(2, address);
                pstmt.setString(3, userId);
            } else {
                pstmt = conn
                        .prepareStatement("update ep_user_info set lastLogoutTime=? where userId=?");
                pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
                pstmt.setString(2, userId);
            }
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    public void updateTryTimes(String userId, int times, String ip) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("update ep_user_info set loginFailedTimes=?,lastLoginTime=?,ipAddress=? where userId=?");
            pstmt.setInt(1, times);
            pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            pstmt.setString(3, ip);
            pstmt.setString(4, userId);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void updatePassword(String userId, String password) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("update ep_user_info set password=? where userId=?");
            pstmt.setString(1, password);
            pstmt.setString(2, userId);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void updateTheme(String userId, String theme) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("update ep_user_info set theme=? where userId=?");
            pstmt.setString(1, theme);
            pstmt.setString(2, userId);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void updateDefaultProject(String userId, String defaultProject) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("update ep_user_info set defaultProject=? where userId=?");
            pstmt.setString(1, defaultProject);
            pstmt.setString(2, userId);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void updateEditorTheme(String userId, String theme) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("update ep_user_info set editorTheme=? where userId=?");
            pstmt.setString(1, theme);
            pstmt.setString(2, userId);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public ProjectBean[] getProject(String userId) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select p.modelId,p.name,p.desp,p.content from ep_model_info p,ep_user_project_info e where e.projectId = p.modelId and e.userId=?");
            stmt.setString(1, userId);
            rs = stmt.executeQuery();
            List<ProjectBean> data = new ArrayList<ProjectBean>();
            while (rs.next()) {
                EntityInfo e = new EntityInfo();
                e.setId(rs.getString(1));
                e.setName(rs.getString(2));
                e.setDescription(rs.getString(3));
                e.setType(EntityType.PROJECT.getName());
                e.setContent(rs.getString(4));
                ProjectBean bean = TransformerFactory.newInstance()
                        .transformFromXml(e);
                data.add(bean);
            }
            ProjectBean[] beans = new ProjectBean[data.size()];
            data.toArray(beans);
            return beans;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public ProjectBean[] getProjectWithID(String projectID, String roleID, String userId) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select p.modelId,p.name,p.desp,p.content from ep_model_info p," +
                            "ep_user_project_info e where e.projectId = ? and e.userId=? and e.roleId LIKE ?" +
                            " and e.projectId=p.modelId and p.`type`='Project'");
            stmt.setString(1, projectID);
            stmt.setString(2, userId);
            stmt.setString(3, "%" + roleID + "%");
            rs = stmt.executeQuery();
            List<ProjectBean> data = new ArrayList<ProjectBean>();
            while (rs.next()) {
                EntityInfo e = new EntityInfo();
                e.setId(rs.getString(1));
                e.setName(rs.getString(2));
                e.setDescription(rs.getString(3));
                e.setType(EntityType.PROJECT.getName());
                e.setContent(rs.getString(4));
                ProjectBean bean = TransformerFactory.newInstance()
                        .transformFromXml(e);
                data.add(bean);
            }
            ProjectBean[] beans = new ProjectBean[data.size()];
            data.toArray(beans);
            return beans;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public ProjectBean[] getAllProject() {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select modelId,name,desp,content from ep_model_info where type='Project' and 1=1");
            rs = stmt.executeQuery();
            List<ProjectBean> data = new ArrayList<ProjectBean>();
            while (rs.next()) {
                EntityInfo e = new EntityInfo();
                e.setId(rs.getString(1));
                e.setName(rs.getString(2));
                e.setDescription(rs.getString(3));
                e.setType(EntityType.PROJECT.getName());
                e.setContent(rs.getString(4));
                ProjectBean bean = TransformerFactory.newInstance()
                        .transformFromXml(e);
                data.add(bean);
            }
            ProjectBean[] beans = new ProjectBean[data.size()];
            data.toArray(beans);
            return beans;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public ProjectBean getProjectDetail(String projectId) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select modelId,name,desp,content from ep_model_info where type='Project' and modelId=? and 1=1");
            stmt.setString(1, projectId);
            rs = stmt.executeQuery();
            ProjectBean data = new ProjectBean();
            while (rs.next()) {
                EntityInfo e = new EntityInfo();
                e.setId(rs.getString(1));
                e.setName(rs.getString(2));
                e.setDescription(rs.getString(3));
                e.setType(EntityType.PROJECT.getName());
                e.setContent(rs.getString(4));
                data = TransformerFactory.newInstance()
                        .transformFromXml(e);
            }
            return data;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    public void updateProject(ProjectBean bean) {
        StringBuilder sb = new StringBuilder();
        StringOutputStream os = new StringOutputStream(sb);
        TransformerFactory.newInstance().transformToXml(bean, os);
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("update ep_model_info set name=?,desp=?,content=? where modelId=?");
            pstmt.setString(1, bean.getName());
            pstmt.setString(2, bean.getDescription());
            pstmt.setString(3, sb.toString());
            pstmt.setString(4, bean.getId());
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public String getRolePermissions(String projectID, String roleID) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select permissions from ep_role_info where roleId=? and projectId=?");
            stmt.setString(1, roleID);
            stmt.setString(2, projectID);
            rs = stmt.executeQuery();
            String access = null;
            if (rs.next()) {
                access = rs.getString(1);
            }
            return access;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public String getTopRole(String projectID, String userID) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("SELECT roleId FROM ep_user_project_info WHERE userId = ? AND projectId = ?");
            stmt.setString(1, userID);
            stmt.setString(2, projectID);
            rs = stmt.executeQuery();
            String access = null;
            if (rs.next()) {
                access = rs.getString(1);
            }
            return access;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public List<ProjectRoleVo> getAllProjectRole(String userID) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("SELECT u.projectId,u.roleId,m.name,m.desp FROM ep_user_project_info u,ep_model_info m " +
                            "WHERE userId = ? AND u.projectId=m.modelId");
            stmt.setString(1, userID);
            rs = stmt.executeQuery();
            List<ProjectRoleVo> projectRoleVo = new ArrayList<>();
            if (rs.next()) {
                ProjectRoleVo vo = new ProjectRoleVo();
                vo.setProjectId(rs.getString(1));
                vo.setRoleIDs(rs.getString(2));
                vo.setProjectName(rs.getString(3));
                vo.setProjectDesp(rs.getString(4));
                projectRoleVo.add(vo);
            }
            return projectRoleVo;
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    public List<Object[]> selectList(String sql, FieldVo... params) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql);
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++)
                    DaoUtils.setValue(pstmt, i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            List<Object[]> data = new ArrayList<Object[]>();
            int size = rsmd.getColumnCount();
            while (rs.next()) {
                Object[] record = new Object[size];
                for (int index = 1; index <= size; index++)
                    record[index - 1] = DaoUtils.getValue(rs, rsmd, index);
                data.add(record);
            }
            return data;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public int update(String sql, List<FieldVo> params) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++)
                    DaoUtils.setValue(pstmt, i + 1, params.get(i));
            }
            return pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public LoginlogVo getOnlineUser(String projectId,String userId, String roleId) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select u.`userId`,u.`name`,r.`name` rolename " +
                            "from ep_user_info u,ep_role_info r where r.projectId = ? and r.roleId=? and u.userId=?");
            stmt.setString(1, projectId);
            stmt.setString(2, roleId);
            stmt.setString(3, userId);
            rs = stmt.executeQuery();
            if (rs.next()) {
                LoginlogVo onlineuser = new LoginlogVo();
                onlineuser.setUserId(rs.getString(1));
                onlineuser.setUserName(rs.getString(2));
                if (LoginUserVo.UserType.ADMIN.getName().equals(roleId)) {
                    onlineuser.setUserType("1");//0,1
                    onlineuser.setRoleName("超级管理员");//超级管理员
                } else {
                    onlineuser.setUserType("0");
                    onlineuser.setRoleName(rs.getString(3));
                }
                return onlineuser;
                }
            return null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public String getPageXml(long id) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn
                    .prepareStatement("select `content` from ep_model_ext_info where 1=1 and id = '" + id +"'");
            rs = stmt.executeQuery();
            if (rs.next()) {
                String xml = rs.getString(1);
                return xml;
            }
            return null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt, rs);
        }
    }

    @Override
    public long insertDevice(String projectId,String xml) {
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn.prepareStatement("insert into ep_model_ext_info (`projectId`, `content`) " +
                    "values(?,?)",Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, projectId);
            pstmt.setString(2, xml);
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            long generateKey = 0;
            while (rs.next()) {
                generateKey = rs.getLong(1);
            }
            return generateKey;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void updateDevice(long id,String projectId,String xml) {
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("update ep_model_ext_info set projectId=?,content=? where id=?");
            pstmt.setString(1, projectId);
            pstmt.setString(2, xml);
            pstmt.setLong(3, id);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public boolean deleteDevice(long id) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            if (id!=0) {
                pstmt = conn.prepareStatement("delete from ep_model_ext_info where 1=1 and id = " + id);
                pstmt.executeUpdate();
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public boolean addUser(String userId, String name, String salt, String password) {
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn.prepareStatement("insert into ep_user_info (`userId`, `name`,`password`,`validDays`, " +
                    "`loginFailedTimes`, `salt`, `theme`, `editorTheme`, `state`) values(?,?,?,?,?,?,?,?,?)",
                    Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, userId);
            pstmt.setString(2, name);
            pstmt.setString(3, password);
            pstmt.setInt(4, 0);
            pstmt.setInt(5, 0);
            pstmt.setString(6, salt);
            pstmt.setString(7, "iceblue_c");
            pstmt.setString(8, "default");
            pstmt.setInt(9, 1);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
        return true;
    }

    @Override
    public boolean addProject(String userId, String projectId, String roleId) {
        PreparedStatement pstmt = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = JdbcTransactions.getConnection(dataSource);
            pstmt = conn
                    .prepareStatement("select roleId from ep_user_project_info where userId=? and projectId = ?");
            pstmt.setString(1, userId);
            pstmt.setString(2, projectId);
            rs = pstmt.executeQuery();
            String roleID = null;
            if (rs.next()) {
                roleID = rs.getString(1);
            }
            if (roleID == null) {
                pstmt = conn.prepareStatement("insert into ep_user_project_info (`userId`, `projectId`,`roleId`) values(?,?,?)",
                        Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, userId);
                pstmt.setString(2, projectId);
                pstmt.setString(3, roleId);
                pstmt.executeUpdate();
            } else {
                //原数据
                List<String> roles = new ArrayList<>();
                if (Strings.isBlank(roleID) == false) {
                    String[] currentRoleID = roleID.split(",");
                    for (String rID : currentRoleID) {
                        roles.add(rID);
                    }
                }
                //新增数据
                if (Strings.isBlank(roleId) == false) {
                    String[] addRoleID = roleId.split(",");
                    for (String rID : addRoleID) {
                        if (roles.contains(rID) == false)
                            roles.add(rID);
                    }
                }
                pstmt = conn
                        .prepareStatement("update ep_user_project_info set roleId=? where userId=? and projectId=?");
                pstmt.setString(1, StringUtil.listToString(roles, ","));
                pstmt.setString(2, userId);
                pstmt.setString(3, projectId);
                pstmt.executeUpdate();
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
        return true;
    }
}
